package de.muc.vanbeek.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collection;

import org.slf4j.Logger;

import de.muc.vanbeek.be.CustomerBE;
import de.muc.vanbeek.logging.LoggerFactory;

/**
 * The data access object encapsulates the data access from the business activities.
 * This object, for example, should not leak the information that it accesses data via 
 * our HSQLDB to its clients.
 * 
 * This object would be part of the data access layer in a three tier architecture, while
 * the business activities would be part of the business layer. 
 *
 */
public class CustomerHsqlDao implements CustomerDao {
	
	private static final Logger sLogger = LoggerFactory.create();

	private final String mUrl;

	private final String mUser;

	private final String mPassword;

	public CustomerHsqlDao(String pUrl, String pUser, String pPassword) {
		mUrl = pUrl;
		mUser = pUser;
		mPassword = pPassword;
	}

	/* (non-Javadoc)
	 * @see de.muc.vanbeek.dao.CustomerDao#getAllCustomers()
	 */
	@Override
	public Collection<CustomerBE> getAllCustomers() {

		// never return null, but an empty collection as default.
		Collection<CustomerBE> result = new ArrayList<>();

		try {
			// TODO: it would seem that we have to explicitly load the driver class,
			// although it should be possible to achieve its use by our database via some
			// configuration steps (?).
			Class.forName("org.hsqldb.jdbcDriver");
		} catch (ClassNotFoundException e) {
			sLogger.error("Treiberklasse nicht gefunden!", e);
			return result;
		}
		
		try (Connection con = DriverManager.getConnection(mUrl, mUser, mPassword);) {
			
			Statement stmt = con.createStatement();

			// Alle Kunden ausgeben
			String sql = "SELECT * FROM Customer";
			ResultSet rs = stmt.executeQuery(sql);

			while (rs.next()) {
				int id = rs.getInt(1);
				String firstName = rs.getString(2);
				String lastName = rs.getString(3);

				CustomerBE customerBE = new CustomerBE(id, firstName, lastName);

				result.add(customerBE);

				sLogger.debug(id + ", " + firstName + " " + lastName);
			}

			// Resultset schließen
			rs.close();

			// Statement schließen
			stmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return result;
	}

	public static void main(String[] args) {
		CustomerDao connection = new CustomerHsqlDao("jdbc:hsqldb:file:hsqldb/customer; shutdown=true", "sa", "");
		connection.getAllCustomers();
	}
}